<?php
jimport('joomla.application.component.model');
class svModelTrangcanhan extends JModel
{
	//------------ tim kiem----------------------------------
	function getAllCanbo($param)
	{
		$clause=" SELECT  cb.*, qlc.*, cv.cv_ten,pb.pb_ten,cd.cd_ten
			    FROM    qlcb_canbo as cb,qlcb_quanlychung as qlc,qlcb_phongban as pb,qlcb_chucvu as cv, qlcb_chucdanh as cd
			    WHERE   cb.cb_id = qlc.qlc_idcanbo  
				AND		qlc.qlc_idphongban=pb.pb_id 
				AND		qlc.qlc_idchucvu=cv.cv_id 
				AND		qlc.qlc_idchucdanh=cd.cd_id  ";
		$donvi=JRequest::getInt('iddonvi');
		if($donvi!=0) $clause=$clause.'  AND pb.pb_iddonviquanly='.$donvi.' ';
		$phongban=JRequest::getInt('idphongban');
		if($phongban!=0) $clause=$clause.' AND qlc.qlc_idphongban='.$phongban.' ';
		$canbo=JRequest::getInt('cb_id');
		if($canbo!=0) $clause=$clause.'  AND cb.cb_id='.$canbo.' ';
		$query=$clause.' ORDER BY '.$param['order'].'  '.$param['order_Dir'].'
				LIMIT '.$param['limitstart'].' , '.$param['limit'];
		$result=$this->_loadObjectList($query);
		return $result;
	}
	function getTotal()
	{
		$clause=" SELECT COUNT(cb_id) 
			    FROM    qlcb_canbo as cb,qlcb_quanlychung as qlc,qlcb_phongban as pb,qlcb_chucvu as cv, qlcb_chucdanh as cd
			    WHERE   cb.cb_id = qlc.qlc_idcanbo  
				AND		qlc.qlc_idphongban=pb.pb_id 
				AND		qlc.qlc_idchucvu=cv.cv_id 
				AND		qlc.qlc_idchucdanh=cd.cd_id  ";
		$donvi=JRequest::getInt('iddonvi');
		if($donvi!=0) $clause=$clause.'  AND pb.pb_iddonviquanly='.$donvi.' ';
		$phongban=JRequest::getInt('idphongban');
		if($phongban!=0) $clause=$clause.' AND qlc.qlc_idphongban='.$phongban.' ';
		$canbo=JRequest::getInt('cb_id');
		if($canbo!=0) $clause=$clause.'  AND cb.cb_id='.$canbo.' ';
		$result=$this->_loadResult($clause);
		return $result;
	}
	//--------------- select list -----------
	function Listcanbo()
	{
		$where="";
		$donvi=JRequest::getInt('iddonvi');
		if($donvi!=0) $where=',qlcb_quanlychung as qlc,qlcb_phongban as pb
		WHERE qlc.qlc_idcanbo = cb.cb_id
		AND  qlc.qlc_idphongban = pb.pb_id
		AND pb.pb_iddonviquanly='.$donvi;
		$phongban=JRequest::getInt('idphongban');
		if($phongban!=0) $where=',qlcb_quanlychung as qlc WHERE qlc.qlc_idcanbo=cb.cb_id AND qlc.qlc_idphongban='.$phongban;
		$query='SELECT DISTINCT cb.cb_id as `key`,cb.cb_ten as `text`  FROM qlcb_canbo as cb '.$where;
		return $this->_getList($query);
	}
	function ListPhongban()
	{
		$where="";
		$donvi=JRequest::getInt('iddonvi');
		if($donvi!=0) $where=' WHERE pb_iddonviquanly='.$donvi;
		$query='SELECT DISTINCT pb_id as `key`,pb_ten as `text`  FROM qlcb_phongban '.$where;
		return $this->_getList($query);
	}
	function ListDonvi()
	{
		$query='SELECT DISTINCT dvql_id as `key`,dvql_ten as `text`  FROM qlcb_donviquanly';
		return $this->_getList($query);
	}
	
	//------------------------------
	function getCanboByACID($acid)
	{
		$query = 'SELECT username FROM #__users'
		. '	WHERE id=66'//.$idcanbo
		;
		$result=$this->_loadResult($query);
		return $result;
	}
	function getCanboByIDs($idcanbo)
	{
		$query = 'SELECT a.*, g.name AS groupname,ac.*,cb.*,pb.pb_ten,qlc.*,cv.cv_ten,cd.cd_ten'
		. ' FROM jos_users AS a'
		. ' INNER JOIN jos_core_acl_aro AS aro ON aro.value = a.id'
		. ' INNER JOIN jos_core_acl_groups_aro_map AS gm ON gm.aro_id = aro.id'
		. ' INNER JOIN jos_core_acl_aro_groups AS g ON g.id = gm.group_id'
		. ' INNER JOIN qlcb_useraccount AS ac ON ac.ac_iduser = a.id'
		. ' INNER JOIN qlcb_canbo AS cb ON cb.cb_id = ac.ac_idcanbo'
		. ' INNER JOIN qlcb_quanlychung AS qlc ON qlc.qlc_idcanbo = cb.cb_id'
		. ' INNER JOIN qlcb_phongban AS pb ON pb.pb_id = qlc.qlc_idphongban'
        . ' INNER JOIN qlcb_chucvu AS cv ON cv.cv_id = qlc.qlc_idchucvu'
        . ' INNER JOIN qlcb_chucdanh AS cd ON cd.cd_id = qlc.qlc_idchucdanh'
		. '	WHERE a.id='.$idcanbo
		;
		$result=$this->_loadObject($query);
		return $result;
	}
	function getAllPhongban()
	{
		$query='SELECT pb_id,pb_ten
				FROM qlcb_phongban
				WHERE 1';
		$result=$this->_loadObjectList($query);
		return $result;
	}
	function getAllChucvu()
	{
		$query='SELECT cv_id,cv_ten
		FROM qlcb_chucvu
		WHERE 1';
		$result=$this->_loadObjectList($query);
		return $result;
	}
	function getAllChucdanh()
	{
		$query='SELECT cd_id,cd_ten
		FROM qlcb_chucdanh
		WHERE 1';
		$result=$this->_loadObjectList($query);
		return $result;
	}
	//----------------------------them ,sua ,xoa -------------------------------

	function getMaxIDCanbo()
	{
		$query='SELECT MAX(cb_id) as max
		FROM qlcb_canbo
		WHERE 1';
		$result=$this->_loadObject($query);
		return $result->max;
	}
	// sua

	function editAcc($password,$username)
	{
		$password;
		$timeupdate=JFactory::getDate()->toMysql();
		// ma hoa md5 password
		jimport( 'joomla.user.helper' );
		$salt = JUserHelper::genRandomPassword(32);
		$crypt = JUserHelper::getCryptedPassword($password, $salt);
		$pass = $crypt.':'.$salt;
		//---
		$acid=JFactory::getUser()->get('id');
		//-------------
		$clpass="";
		$cluser="";
		if(strlen($username)>=8) $cluser=",username='".$username."' ";
		if(strlen($password)>=6) $clpass=",password='".$pass."' ";
		echo $query="UPDATE #__users SET lastvisitDate='".$timeupdate."' ".$cluser." ".$clpass." WHERE id=".$acid;
		$message='Edit 1 item ';
		$result=$this->_Execute($query, $message);
		return $result;	
	}
	// cac ham phuc vu truy van CSDL
	function _loadResult($query)
	{
		$db= JFactory::getDBO();
		$db->setQuery($query);
		$result=$db->loadResult();
		return $result;
	}
	function _loadObject($query)
	{
		$db= JFactory::getDBO();
		$db->setQuery($query);
		$result=$db->loadObject();
		return $result;
	}
	function _loadObjectList($query)
	{
		$db= JFactory::getDBO();
		$db->setQuery($query);
		$result=$db->loadObjectList();
		return $result;
	}
	function _Execute($query,$message)
	{
		$db= JFactory::getDBO();
		$db->setQuery($query);
		try {
			$db->query();
			return $message." thành công ";
		}
		catch (Exception $e){
			return $message." Không thành công! Vui lòng thử lại.";
		}
	}
}
?>